//201902104011王雪梦
package bysj.dao;

import bysj.domain.User;
import bysj.domain.authority.Actor;
import bysj.service.ActorService;
import util.JdbcHelper;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.Collection;
import java.util.HashSet;


public final class UserDao {
    private static UserDao userDao = new UserDao();

    private UserDao() {
    }

    public static UserDao getInstance() {
        return userDao;
    }
    public Collection<User> findAll(Connection connection) throws SQLException {
        Collection<User> desiredTeachers = new HashSet<User>();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM user");
        //获得连接对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行SQL查询语句并获得结果集对象
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            User user = new User("null","null");
            //user.setId(resultSet.getInt(1));
            //为school对象的description赋值
            user.setUsername(resultSet.getString(2));
            //为school对象的no赋值
            user.setPassword(resultSet.getString(3));
            int actor_id = resultSet.getInt(4);
            user.setActor_id(actor_id);
            user.setLoginTime(LocalDateTime.now());
            //为school对象的remarks赋值
            //teacher.setUser(user);
            //将school增加到schools集合
            desiredTeachers.add(user);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, preparedStatement);
        return desiredTeachers;
    }
    public static User find(int id) throws SQLException {
        User user = new User("username","password");
        Connection connection = JdbcHelper.getConn();
        //创建sql语句
        String user_sql = "SELECT * FROM user WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(user_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            user.setId(resultSet.getInt(1));
            //为school对象的description赋值
            user.setUsername(resultSet.getString(2));
            //为school对象的no赋值
            user.setPassword(resultSet.getString(3));
            int actor_id = resultSet.getInt(4);
            user.setActor_id(actor_id);
            user.setLoginTime(LocalDateTime.now());
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt, connection);
        return user;
    }
    public int add(User user, Connection connection) throws SQLException {
        int rowAffected = 0;
        int idOfAddedUser = 0;
                //创建语句对象，字段LastLoginTime为‘时间戳’类型，无须手动写入
                PreparedStatement preparedStatement = connection.prepareStatement("insert into user" + "(username,password,actor_id)" + "values (?,?,?)", Statement.RETURN_GENERATED_KEYS);
                preparedStatement.setString(1,user.getUsername());
                preparedStatement.setString(2,user.getPassword());
                preparedStatement.setInt(3,user.getActor().getId());
                System.out.println(user.getActor().getId());
                //执行语句
                rowAffected = preparedStatement.executeUpdate();
                ResultSet resultSet = preparedStatement.getGeneratedKeys();
                if(resultSet.next()){
                    //读取第一个主键
                    idOfAddedUser = resultSet.getInt(1);
                }
        //事务未结束，不能释放资源
        return idOfAddedUser;

    }
    public boolean update(User user,Connection connection)throws SQLException {
        //创建sql语句
        String updateUser_sql = "UPDATE user SET username=?, password=?, actor_id=? WHERE id=?";
        //在该连接上创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(updateUser_sql);
        //为预编译参数赋值
        pstmt.setString(1,user.getUsername());
        pstmt.setString(2,user.getPassword());
        pstmt.setInt(3,user.getActor().getId());
        pstmt.setInt(4,user.getId());
        //执行预编译语句的executeUpdate方法,获得修改的记录行数
        int affectedRowNum=pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        //返回school对象是否修改成功
        return affectedRowNum>0;
    }
    public Actor login(Connection connection, User user) throws SQLException {
        //创建Teacher类型的引用，暂不创建对象
        Actor currentActor = null;
        String sql_login = "SELECT * FROM user WHERE username =? and password=?";
        PreparedStatement pstmt_login =
                connection.prepareStatement(sql_login);
        pstmt_login.setString(1, user.getUsername());
        pstmt_login.setString(2, user.getPassword());
        ResultSet resultSet_login = pstmt_login.executeQuery();
        //如果表中存在id对应的记录，则获得表中的actor_id，并获得对应的Actor对象
        if (resultSet_login.next()) {
            int actor_id = resultSet_login.getInt("actor_id");
            currentActor = ActorService.getInstance().find(actor_id);
        }
        return currentActor;
    }
}